﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace HotelManagement.Class
{
    public class HousekeepingDBManager
    {
        public static int insertHousekeepingSchedule(HousekeepingSchedule hs)
        {
            int rowsInserted = 0;


            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();

                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO HousekeepingSchedule (scheduleID, ScheduleDate, GeneralMaintenanceStaffID, RoomMaintenanceStaffID, EstateMaintenanceStaffID, SecurityMaintenanceStaffID, Description)" +
                    " VALUES (@scheduleID, @date, @generalMaintenanceStaffID, @roomMaintenanceStaffID, @estateMaintenanceStaffID, @securityMaintenanceStaffID, @description)";

                comm.Parameters.AddWithValue("@scheduleID", hs.ScheduleID);
                comm.Parameters.AddWithValue("@date", hs.Date);
                comm.Parameters.AddWithValue("@generalMaintenanceStaffID", hs.GeneralMaintenanceStaffID);
                comm.Parameters.AddWithValue("@roomMaintenanceStaffID", hs.RoomMaintenanceStaffID);
                comm.Parameters.AddWithValue("@estateMaintenanceStaffID", hs.EstateMaintenanceStaffID);
                comm.Parameters.AddWithValue("@securityMaintenanceStaffID", hs.SecurityMaintenanceStaffID);
                comm.Parameters.AddWithValue("@description", hs.Description);


                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }



        public static HousekeepingSchedule getHousekeepingSchedulebyScheduleId(string id)
        {
            HousekeepingSchedule hs = null;

            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM HousekeepingSchedule WHERE scheduleID=@scheduleID";
                comm.Parameters.AddWithValue("@scheduleID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    hs = new HousekeepingSchedule();
                    hs.ScheduleID = (string)dr["scheduleID"];
                    hs.Date = (DateTime)dr["ScheduleDate"];
                    hs.GeneralMaintenanceStaffID = (string)dr["GeneralMaintenanceStaffID"];
                    hs.RoomMaintenanceStaffID = (string)dr["RoomMaintenanceStaffID"];
                    hs.EstateMaintenanceStaffID = (string)dr["EstateMaintenanceStaffID"];
                    hs.SecurityMaintenanceStaffID = (string)dr["SecurityMaintenanceStaffID"];
                    hs.Description = (string)dr["Description"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return hs;

        }

        public static int updateHousekeepingSchedule(HousekeepingSchedule hs)
        {
            int rowsUpdated = 0;


            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE HousekeepingSchedule SET ScheduleID=@scheduleID, ScheduleDate=@scheduleDate,  GeneralMaintenanceStaffID=@generalMaintenanceStaffID, RoomMaintenanceStaffID=@roomMaintenanceStaffID, EstateMaintenanceStaffID=@estateMaintenanceStaffID, SecurityMaintenanceStaffID=@securityMaintenanceStaffID, Description=@description WHERE ScheduleID=@scheduleID";

                comm.Parameters.AddWithValue("@scheduleID", hs.ScheduleID);
                comm.Parameters.AddWithValue("@scheduleDate", hs.Date);
                comm.Parameters.AddWithValue("@generalMaintenanceStaffID", hs.GeneralMaintenanceStaffID);
                comm.Parameters.AddWithValue("@roomMaintenanceStaffID", hs.RoomMaintenanceStaffID);
                comm.Parameters.AddWithValue("@estateMaintenanceStaffID", hs.EstateMaintenanceStaffID);
                comm.Parameters.AddWithValue("@securityMaintenanceStaffID", hs.SecurityMaintenanceStaffID);
                comm.Parameters.AddWithValue("@description", hs.Description);

                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsUpdated;
        }
        public static int deleteHousekeepingSchedule(string scheduleID)
        {
            int rowsDeleted = 0;

            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from HousekeepingSchedule where scheduleID=@scheduleID";
                comm.Parameters.AddWithValue("@scheduleID", scheduleID);
                rowsDeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsDeleted;
        }
    }
}